﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using QUANLIBANHANG.ENTITY;
using System.Data.SqlClient;
namespace QUANLIBANHANG.DAL
{
    public class SanphamDAL
    {
        private static SqlConnection conn = ConnectionSQL.conn;
           // new SqlConnection(@"Data Source=(local);Initial Catalog=QUANLIBANHANG;Integrated Security=True");
       // Hàm insert sản phẩm
        public static void insertSP(SanphamEntity sp)
        {
             string dk= @"INSERT INTO SANPHAM (TENSP, DVTINH, HANGSX, HINHANH, GIA, SOLUONG, NUOCSX, LOAISP) VALUES (@TENSP, @DVTINH, @HANGSX, @HINHANH, @GIA, @SOLUONG, @NUOCSX, @LOAISP)";

            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(dk, conn);
               
                command.Parameters.AddWithValue("@TENSP",sp.Tensp);
                command.Parameters.AddWithValue("@DVTINH", sp.Dvtinh);
                command.Parameters.AddWithValue("@HANGSX", sp.Hangsx);
                command.Parameters.Add("@HINHANH",SqlDbType.Image,sp.Hinhanh.Length).Value=sp.Hinhanh;
                command.Parameters.AddWithValue("@GIA",sp.Gia);
                command.Parameters.AddWithValue("@SOLUONG", sp.Soluong);
                command.Parameters.AddWithValue("@NUOCSX", sp.Nuocsx);
                command.Parameters.AddWithValue("@LOAISP", sp.Loaisp);
                command.ExecuteNonQuery();
                conn.Close();

            }
            catch(Exception ex)
            {
                throw ex;
            }
            
        }
        // Hàm update sản phẩm 
        public static void updateSP(SanphamEntity sp)
        {
            string sql = @"UPDATE SANPHAM SET TENSP =@TENSP, DVTINH =@DVTINH, HANGSX =@HANGSX, HINHANH =@HINHANH, GIA =@GIA, SOLUONG =@SOLUONG, LOAISP =@LOAISP, NUOCSX =@NUOCSX WHERE MASP='"+sp.Masp+"'";
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddWithValue("@TENSP", sp.Tensp);
                command.Parameters.AddWithValue("@DVTINH", sp.Dvtinh);
                command.Parameters.AddWithValue("@HANGSX", sp.Hangsx);
                command.Parameters.Add("@HINHANH", SqlDbType.Image, sp.Hinhanh.Length).Value = sp.Hinhanh;
                command.Parameters.AddWithValue("@GIA", sp.Gia);
                command.Parameters.AddWithValue("@SOLUONG", sp.Soluong);
                command.Parameters.AddWithValue("@NUOCSX", sp.Nuocsx);
                command.Parameters.AddWithValue("@LOAISP", sp.Loaisp);
                command.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // Hàm delete sản phẩm
        public static void deleteSP(SanphamEntity sp)
        {
            
            try
            {
                
                ConnectionSQL.commandSQL("DELETE FROM SANPHAM WHERE MASP='" + sp.Masp + "'");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // Hàm lấy về DataTable sản phẩm 
        public static DataTable getTableSP(string dk)
        {
            try
            {
                return ConnectionSQL.getTable("SELECT * FROM SANPHAM " + dk);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable getSP()
        {
            try
            {
                return ConnectionSQL.getTablePro("PRO_getsp");
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }

        public static int  deleteSP1(SanphamEntity sp)
        {
            List<SqlParameter> paramenters= new List<SqlParameter>();
            paramenters.Add(new SqlParameter("@MyMASP",sp.Masp));
            try
            {
               return ConnectionSQL.executeQueryParameter("PRO_deleteSP", paramenters);
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
 
        }
        public static DataTable getSPWhere(SanphamEntity sp)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paramenters = new List<SqlParameter>();
            paramenters.Add(new SqlParameter("@MASP",sp.Masp));
            try
            {
                return ConnectionSQL.getTablePro("PRO_getSPWhereMasp", paramenters);
                
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            
        }
    }
}
